using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace StudentManagementApplication.DAO
{
    public class FeeDAO : BaseDAO
    {
        public FeeDAO()
        {
        }

        public Entities.Fee CreateFeeFromReader(IDataReader reader)
        {
            Entities.Fee fee = new Entities.Fee();
            fee.StudentID = (string)reader["cStudentID"];
            fee.FeeType = (string)reader["cTypeID"];
            fee.FeePaid = (int)reader["iFeePaid"];
            return fee;
        }

        public void InsertFee(Entities.Fee fee)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcInsertFee", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", fee.StudentID));
                command.Parameters.Add(new SqlParameter("@cTypeID", fee.FeeType));
                command.Parameters.Add(new SqlParameter("@iFeePaid", fee.FeePaid));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                    
            }
        }

        public void UpdateFee(Entities.Fee fee)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcUpdateFee", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", fee.StudentID));
                command.Parameters.Add(new SqlParameter("@cTypeID", fee.FeeType));
                command.Parameters.Add(new SqlParameter("@iFeePaid", fee.FeePaid));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public void DeleteFee(String sStudentID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcDeleteFee", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", sStudentID));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public Entities.Fee GetFeeByStudentID(string sStudentID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetFeeByStudentID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cStudentID", sStudentID));

                connection.Open();
                Entities.Fee fee = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        fee = CreateFeeFromReader(reader);
                    }
                    //else
                    //    throw;
                }
                return fee;
            }
        }

        public List<Entities.Fee> GetAllFees()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllFees", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                List<Entities.Fee> fees = new List<Entities.Fee>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        fees.Add(CreateFeeFromReader(reader));
                    }
                }
                return fees;
            }
        }

        public DataTable GetAllFees2()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllFees2", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetFeeByStatus(int iStatus)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetFeeByStatus", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("iFeePaid", iStatus));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetFeeByClassID(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetFeeByClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cClassID", sClassID));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetFeeByStatusAndClassID(int iFeePaid, string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetFeeByStatusAndClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("iFeePaid", iFeePaid));
                command.Parameters.Add(new SqlParameter("cClassID", sClassID));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

    }
}
